package org.databasefacade.examples;

import org.databasefacade.DatabaseFacade;
import org.databasefacade.select.Comparison;
import org.databasefacade.statements.UpdateStatement;
import org.databasefacade.table.column.Column;
import org.databasefacade.table.system.DbfFieldRegistryTable;
import org.databasefacade.table.system.DbfTableRegistryTable;

public class ExampleUpdateStatements {
	public DbfTableRegistryTable tabTable;
	public DbfFieldRegistryTable colTable;
	
	/**
	 * Example: UPDATE <table1>
	 *             SET <field1> = <field2>
	 *           WHERE <field1> IS NULL
	 */
	public void example1(int translatorType) {
		// DatabaseFacade.translatorTestingInit(translatorType);	
		UpdateStatement statement;
		
		// Setup table aliases		
		colTable.setTableAlias("T1");

	
		// select * from <table1>
		statement = new UpdateStatement(colTable);
		Column field1 = colTable.getColumn(DbfFieldRegistryTable.FIELD_NAME);
		Column field2 = colTable.getColumn(DbfFieldRegistryTable.FIELD_NAME);
		statement.set(field1, field2);
		statement.set(field1, field2);
		statement.set(field1, field2);
		
		// Where
		Comparison comp = new Comparison();
		comp.isEqualNull(field1);
		statement.where(comp);

		
		// Print results
		System.out.println("\n\n" + statement.getSQL());
	}
	
	/**
	 * Example: UPDATE <table1>
	 *             SET <table1>.<field1> = T2.<field2>
	 *            FROM <table1>
	 *                 JOIN <table2> T2
	 *                   ON <table1>.<FK> = T2.<PK>
	 *           WHERE <table1>.<field1> IS NULL
	 *             AND <table2>.<field2> IS NOT NULL
	 */
	public void example2(int translatorType) {
		// DatabaseFacade.translatorTestingInit(translatorType);	
		UpdateStatement statement;
		
		// Setup table aliases. Update in some databases do not allow aliases so
		// it is best to setup the update table with its own table name as an alias.
		colTable.setTableAlias(colTable.getTableName());
		tabTable.setTableAlias("T2");

	
		// update <table1> set <table1>.<field1> = <table2>.<field2>
		statement = new UpdateStatement(colTable);
		Column field1 = colTable.getColumn(DbfFieldRegistryTable.FIELD_NAME);
		Column field2 = tabTable.getColumn(DbfTableRegistryTable.DATABASE_TABLE_NAME);
		statement.set(field1, field2);

		// join
		statement.from().join(tabTable);
		
		// Where
		Comparison comp = new Comparison();
		comp.isEqualNull(field1);
		statement.where(comp);
		statement.where().and().isNotEqualNull(field2);

		
		// Print results
		System.out.println("\n\n" + statement.getSQL());
	}

	public void main(String[] args) throws Exception {
		DatabaseFacade dbFacade = new DatabaseFacade(DatabaseFacade.ANSI_SQL_92);		
		tabTable = new DbfTableRegistryTable(dbFacade.getDatabaseHelper());
		colTable = new DbfFieldRegistryTable(dbFacade.getDatabaseHelper());
		System.out.println("\n\n****************  ANSI SQL 92 ****************");
		example2(DatabaseFacade.ANSI_SQL_92);
		System.out.println("\n\n****************  SQL Server ****************");
		example2(DatabaseFacade.MSSQL);
		System.out.println("\n\n****************  MySQL ****************");
		example2(DatabaseFacade.MYSQL);
		System.out.println("\n\n****************  Oracle ****************");
		example2(DatabaseFacade.ORACLE);
		System.exit(0);
	}
}
